# -*- coding: utf-8 -*-
import pymysql
import xlrd
class dbClection:
    connection=pymysql.connect(host='10.22.31.50',
                               user='ods_cq_read',
                               password='02mjyis5',
                               db='lifeasia',
                               port=6033,
                               charset='utf8',
                               cursorclass=pymysql.cursors.DictCursor)

    def getOrgid(self):

        cur = self.connection.cursor()
        filename = r'C:\Users\tanjm.ext\PycharmProjects\OWB\TestData\Data.xls'
        excel_open = xlrd.open_workbook(filename)
        table = excel_open.sheet_by_name('Sheet1')
        rows = table.nrows
        for i in range(2, rows):
            row_content = table.row_values(i)
            refno = row_content[4]
        # refno='LA0100000103252'
        # sql ='SELECT stg_zchrpf.CHDRNUM,stg_zchrpf.REFNO,stg_letcpf.LETTYPE,stg_chdrpf.STATCODE FROM stg_zchrpf LEFT JOIN stg_letcpf ON stg_zchrpf.CHDRNUM=stg_letcpf.CHDRNUM LEFT JOIN stg_chdrpf ON stg_zchrpf.CHDRNUM=stg_chdrpf.CHDRNUM WHERE stg_zchrpf.REFNO = "LA0100000103252" '
        try:
            cur.execute('SELECT stg_zchrpf.REFNO,stg_zchrpf.CHDRNUM,stg_letcpf.LETTYPE,stg_chdrpf.STATCODE FROM stg_zchrpf LEFT JOIN stg_letcpf ON stg_zchrpf.CHDRNUM=stg_letcpf.CHDRNUM LEFT JOIN stg_chdrpf ON stg_zchrpf.CHDRNUM=stg_chdrpf.CHDRNUM WHERE stg_zchrpf.REFNO = %s',(refno))
            results = cur.fetchall()
            # for row in results:
            #     CHDRNUM=row[0]
            #     REFNO=row[1]
            #     LETTYPE=row[2]
            #     STATCODE=row[3]
            #     print('CHDRNUM=%s,REFNO=%s,LETTYPE=%s,STATCODE=%s' %(CHDRNUM,REFNO,LETTYPE,STATCODE))


            # for row in results:
            #     org_id = row['id']
            #     return org_id
            #     print(org_id)
            # self.connection.commit()



            return results
        except:
            print('Error:unable to fetch data')

        self.connection.close()